#ifndef DB_H  //bi mian chong fu bao han
#define DB_H
#include<stdio.h>
#include<stdlib.h>
#include <string.h>
#include<sqlite3.h>
#include<time.h>

#define MAX_USERS 100
#define USERNAME_LEN 50
#define PASSWORD_LEN 50
#define EMAIL_LEN 100
#define PHONE_LEN 20

#define DB_FILENAME "stu.db"
#define DEFAULT_STUDENT_PASSWORD "123456"  // 统一初始密码

#define LEN 128

int user_index = -1;

//成绩模块结构体wwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwww

// 学生成绩结构体（一行多学科）
typedef struct 
{
    char id[60];     // 学号
    char name[50];   // 姓名
    char class[50];  // 班级
    float chinese;   // 语文
    float math;      // 数学
    float english;   // 英语
    float physics;   // 物理
    float chemistry; // 化学
    float biology;   // 生物
    float total;     // 总成绩
} Student;

// 关闭数据库
void close_database();

//成绩模块函数wwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwww


//用户模块结构体
typedef struct {
    char id[20];
    char username[50];
    char password[50];
    int is_admin;      // 0-学生，1-管理员;
    char class_name[30];
    char phone[20];    
    char email[50];    
    //float math, english, science;
} User;

typedef struct 
{
    sqlite3 *db;
    User users[MAX_USERS];
    int count;
} UserDatabase;

// 班级成绩统计结构
typedef struct 
{
    char class_name[30];
    int count;
    float chinese_sum;
    float math_sum;
    float english_sum;
    float physics_sum;
    float chemistry_sum;
    float biology_sum;
    float total_sum;
    
    // 最高分和最低分
    float max_total;
    float min_total;
    float max_chinese;
    float min_chinese;
    float max_math;
    float min_math;
    float max_english;
    float min_english;
    float max_physics;
    float min_physics;
    float max_chemistry;
    float min_chemistry;
    float max_biology;
    float min_biology;
} ClassStats;

// 初始化数据库
int init_database(UserDatabase *db);
// 成绩管理模块
int add_grade(UserDatabase *db, Student *stu);                // 录入成绩
void input_grade(UserDatabase *db);
void view_grades(UserDatabase *db, int user_index);
int query_grade(const char *id_or_name, Student *result); // 查询成绩
int stat_class(const char *class_name, float *avg, float *max, float *min); // 统计班级

void analyze_class_grades(UserDatabase *db);
void export_student_report(UserDatabase *db, int user_index);
const char* get_current_time();
void init_class_stats(ClassStats *stats);
void update_class_stats(ClassStats *stats, float chinese, float math, float english,
                       float physics, float chemistry, float biology, float total);
                       

//shengming fangzai touwenjian

/*const char* create_users_table="create table if not exists users(id integer primary key autoincrement,"
                               "username text unique not null,"
                               "password text not null,"
                               "role text not null check(role IN ('admin','user')),"
                               "email text,"
                               "time datetime default (datatime('now')));";
*/


const char* create_message_table = "create table if not exists message(id integer primary key,name text not null,sex text check(sex = 'male' or sex = 'female'),age integer check(age > 0),class integer not null);";

/*const char* create_stu_grade="create table if not exists grades(student_id integer primary key unique not null,"
                             "name text not null,"
                             "subject text not null,"
                             "score real not null,"
                             "total_score real not null);";
*/
const char *create_stu_grade = 
        "CREATE TABLE IF NOT EXISTS grades ("
        "id TEXT PRIMARY KEY UNIQUE,"
        "name TEXT,"
        "class TEXT,"
        "chinese REAL,"
        "math REAL,"
        "english REAL,"
        "physics REAL,"
        "chemistry REAL,"
        "biology REAL,"
        "total REAL"
        ");";


int insert_stu_info(const char *id,const char *name,const char *sex,const char *age,const char *class);
int update_stu_info(const char *id_up,const char *name_up,const char *sex,const char *age,const char *class,const char *id_pre,const char *name_pre);
int delete_stu_info(const char *id,const char *name);

//int print_user_info(void* data,int argc,char** argv,char** colname);
//int verifyUser(const char *username,const char *passwd);

#endif